Failed to join database… to the Availability Group

Introduction

In this lab, you will try to create an Availability Group that fails at the join database to the availability group step. The goal is to try and identify the cause of the failure and fix the issue.

Objectives

At the end of this lab, you will be able to:

Understand the cause of the Availability Group creation failure.
Fix the issue and create a new Availability Group.

Estimated Time

45 minutes

Logon Information

Use the following credentials to login into virtual environment

Username: corpnet\cluadmin
Password: Pa$$w0rd

Before starting the training module, we recommend that you launch the labs and give them some time to stabilize. Please be aware that sometimes the AG may be in a resolving state and AG Replicas may be in a disconnected state. This is a platform issue and should stabilize after a few minutes

Environment review

Before you begin with the first exercise in the lab, let's review the lab environment.

In the lab, you have one Domain Controller and 3 nodes + 1 client computer.
AlwaysOnN1 and AlwaysOnN2 nodes are in the primary Datacenter.
AlwaysOnN3 is in the secondary datacenter.
For this lab, both the datacenters are in the same subnet.
Each node has Windows Server 2022 O/S installed.
SQL Server 2022 Standalone instances are installed on all the 3 nodes (i.e. AlwaysOnN1, AlwaysOnN2, AlwaysOnN3).
SQL Service logon account details
- SQL Service Logon account: corpnet\SQLSvc
- Password: Pa$$w0rd

Back to the list of Labs

Exercise 2: Identify the cause of the failure and fix the issue

In this exercise, you will learn how to Identify the cause of the failure and fix the issue.

Tasks

Find the root cause using the guidelines and cheat sheet provided below.

Guidelines

This is a non-guided activity and the attendees are expected to try and troubleshoot this issue on their own.
You can use any resources (including the internet or your own scripts), to troubleshoot the issue.
You can use the tools discussed in the first module to help troubleshoot the issue.
The possible causes discussed earlier in the lesson can be used as guidance for troubleshooting.
The instructor will discuss the troubleshooting steps, cause and solution in detail after this lab session.
You might have to login directly on the individual nodes to troubleshoot the issue.
Ask yourself the below questions:
- Where do you start?
- What logs will provide me additional insights?
- What tools can I use to troubleshoot this issue?
- Could one or more of the possible causes discussed earlier be the issue here?

Cheat Sheet

Use some commands that could be used for troubleshooting the issue.

This is not a complete list of commands/tools to help troubleshoot this issue. There are various ways/methods/approaches to troubleshoot an issue. These commands/tools referenced here could be used to look up/identify useful information for this lab.

SQL Service logon account details:

SQL Service Logon account: corpnet\SQLSvc
Password: Pa$$w0rd

To detect if the endpoints are created or what port number is being used, query for the database_mirroring endpoints at the primary and the secondary replicas. To be run in SQLCMD mode: SSMS - Query - SQLCMD mode.

SQL
:Connect AlwaysOnN1  
select name, state_desc, port from sys.tcp_endpoints where type_desc='DATABASE_MIRRORING'  
go  

:Connect AlwaysOnN2  
select name, state_desc, port from sys.tcp_endpoints where type_desc='DATABASE_MIRRORING'  
go

:Connect AlwaysOnN3
select name, state_desc, port from sys.tcp_endpoints where type_desc='DATABASE_MIRRORING'  
go

Command to create end point, if it does not exist:

SQL
:Connect AlwaysOnN1  
create endpoint [Hadr_endpoint]  
state=started  
as tcp (listener_port = 5022, listener_ip = all)  
for database_mirroring (role = all, authentication = windows negotiate, encryption = required algorithm aes)  
go

To detect if the endpoints are started, query them at the primary and the secondary.

SQL
:Connect AlwaysOnN1  
select name, state_desc, port FROM sys.tcp_endpoints where name='hadr_endpoint'  
go  

:Connect AlwaysOn2  
select name, state_desc, port from sys.tcp_endpoints where name='hadr_endpoint'  
go

:Connect AlwaysOn3  
select name, state_desc, port from sys.tcp_endpoints where name='hadr_endpoint'  
go

If you find an endpoint is not running, start it:

SQL
:Connect AlwaysOnN1  
alter endpoint [Hadr_endpoint] state = started
go

:Connect AlwaysOnN2  
alter endpoint [Hadr_endpoint] state = started
go

:Connect AlwaysOnN3  
alter endpoint [Hadr_endpoint] state = started
go

Command to view SQL Error logs:

SQL
:Connect AlwaysOnN1
Exec Xp_readerrorlog
go

:Connect AlwaysOnN2
Exec Xp_readerrorlog
go

:Connect AlwaysOnN3
Exec Xp_readerrorlog
go

Command to query replica states on the secondary.

Query the sys.dm_hadr_availability_replica_states for the last_connect_error_number which may help you diagnose the join issue.

Depending on which replica was having difficulty communicating, you should query both the primary and secondary for the local replica:

SQL
select r.replica_server_name, r.endpoint_url,  
rs.connected_state_desc, rs.last_connect_error_description,  
rs.last_connect_error_number, rs.last_connect_error_timestamp  
from sys.dm_hadr_availability_replica_states rs join sys.availability_replicas r  
on rs.replica_id=r.replica_id  
where rs.is_local=1

To look at firewall settings, on each node you can search for firewall:

Congratulations!

You have successfully completed this exercise. Click Next to advance to the next lab.

Failed to join database… to the Availability Group

Introduction

Objectives

Estimated Time

Logon Information

Environment review

Exercise 1: Create an Availability Group between 3 replicas

Tasks

Congratulations!

Exercise 2: Identify the cause of the failure and fix the issue

Tasks

Guidelines

Cheat Sheet

Congratulations!